package com.mashen.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.mashen.dao.model.BerecommandEntity;
import com.mashen.dao.model.RecommandEntity;
import com.mashen.jdbc.ConnectionFactory;

public class RecommandDao {

	private String addSql = "INSERT INTO weixin_recommand ( openid, nickname, score, mediaid,picresult) VALUES (?, ?, ?, ?,?)";

	/**
	 * 增加实体
	 * 
	 * @param entity
	 * @throws Throwable
	 */
	public boolean add(RecommandEntity entity) throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(addSql);
			// openid, nickname, score, mediaid
			stat.setString(1, entity.getOpenid());
			stat.setString(2, entity.getNickname());
			stat.setInt(3, entity.getScore());
			stat.setString(4, entity.getMediaid());
			stat.setString(5,entity.getPicresult());
			stat.executeUpdate();
			return true;
		} finally {
			ConnectionFactory.closeConnection(conn);
		}

	}

	private String deleteSql = "delete from weixin_recommand where id=?";

	/**
	 * 删除实体
	 * 
	 * @param id
	 * @return
	 * @throws Throwable
	 */
	public boolean delete(int id) throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(deleteSql);
			stat.setInt(1, id);
			stat.executeUpdate();
			return true;
		} finally {
			ConnectionFactory.closeConnection(conn);
		}
	}

	private String updateSql = "UPDATE weixin_recommand SET openid =?, nickname =?, score =?, mediaid =?,picresult=? WHERE id =?";

	/**
	 * 修改实体
	 * 
	 * @param entity
	 * @return
	 * @throws Throwable
	 */
	public boolean update(RecommandEntity entity) throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(updateSql);
			// openid, nickname, score, mediaid
			stat.setString(1, entity.getOpenid());
			stat.setString(2, entity.getNickname());
			stat.setInt(3, entity.getScore());
			stat.setString(4, entity.getMediaid());
			stat.setString(5, entity.getPicresult());
			stat.setInt(6, entity.getID());
			stat.executeUpdate();
			return true;
		} finally {
			ConnectionFactory.closeConnection(conn);
		}
	}

	private String getSql = "SELECT ID, openid, nickname, score, mediaid, createtime FROM weixin_recommand WHERE id =?";

	/**
	 * 查询实体
	 * 
	 * @param id
	 * @return
	 * @throws Throwable
	 */
	public RecommandEntity get(int id) throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(getSql);
			stat.setInt(1, id);
			ResultSet rs = stat.executeQuery();
			if (rs.next()) {
				RecommandEntity entity = new RecommandEntity();
				entity.setID(rs.getInt("id"));// 大小写不敏感
				entity.setOpenid(rs.getString("openid"));
				entity.setNickname(rs.getString("nickname"));
				entity.setMediaid(rs.getString("mediaid"));
				entity.setCreatetime(rs.getDate("createtime"));
				entity.setScore(rs.getInt("score"));
				return entity;
			}
		} finally {
			ConnectionFactory.closeConnection(conn);
		}
		return null;
	}

	private String getByOpenIdSql = "SELECT ID, openid, nickname, score, mediaid, createtime,picresult FROM weixin_recommand WHERE openid =?";

	/**
	 * 查询实体
	 * 
	 * @param id
	 * @return
	 * @throws Throwable
	 */
	public RecommandEntity getByOepnid(String openid) throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(getByOpenIdSql);
			stat.setString(1, openid);
			ResultSet rs = stat.executeQuery();
			if (rs.next()) {
				RecommandEntity entity = new RecommandEntity();
				entity.setID(rs.getInt("id"));// 大小写不敏感
				entity.setOpenid(rs.getString("openid"));
				entity.setNickname(rs.getString("nickname"));
				entity.setMediaid(rs.getString("mediaid"));
				entity.setCreatetime(rs.getDate("createtime"));
				entity.setPicresult(rs.getString("picresult"));
				entity.setScore(rs.getInt("score"));
				return entity;
			}
		} finally {
			ConnectionFactory.closeConnection(conn);
		}
		return null;
	}
	/**
	 * 给推荐人累加积分，增加推荐记录
	 * @param recom
	 * @param berecom
	 * @return
	 */
	private String addBecomSql="INSERT INTO weixin_berecommand ( openid, nickname, recommandid ) VALUES (?,?,?)";
	private String updateScore="update weixin_recommand set score=score+1 where id=?";
	public boolean updateScore(BerecommandEntity berecom){
		Connection conn = null;
		try {
			conn = ConnectionFactory.getInstance()
					.getConnection();
			conn.setAutoCommit(false);//开启事务
			//1添加被推荐记录
			PreparedStatement berecomstat=conn
					.prepareStatement(
					addBecomSql);
			//openid, nickname, recommandid
			berecomstat.setString(1, berecom.getOpenid());
			berecomstat.setString(2, berecom.getNickname());
			berecomstat.setInt(3,berecom.getRecommandid());
			berecomstat.executeUpdate();
			//2累加积分
			PreparedStatement recomstat=
					conn.prepareStatement(updateScore);
			
			recomstat.setInt(1,berecom.getRecommandid());
			recomstat.executeUpdate();
			conn.commit();//提交事务
			return true;
		}catch(Throwable e){
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}//事务回滚
			e.printStackTrace();
		}finally {
			ConnectionFactory.closeConnection(conn);
		}
		return false;
		
	}
	
	String testSql = "select * from test_user limit 1";
	public boolean test() throws Throwable {
		Connection conn = ConnectionFactory.getInstance().getConnection();
		try {
			PreparedStatement stat = conn.prepareStatement(testSql);
			ResultSet rs = stat.executeQuery();
			if (rs!=null&&rs.next()) {
				return true;
			}else{
				return false;
			}
		} finally {
			ConnectionFactory.closeConnection(conn);
		}
	}
	
	
	public static void main(String[] args) throws Throwable {
		RecommandDao dao = new RecommandDao();

		
		/* RecommandEntity entity=new RecommandEntity(); entity.setID(3);
		 entity.setOpenid("openid"); 
		 entity.setNickname("中文");
		 entity.setScore(100); 
		 entity.setMediaid("mediaid");
		 entity.setID(4);*/
		 //dao.add(entity);
		 
		 //dao.delete(2); 
		 //dao.update(entity);
		 
		//RecommandEntity entity = dao.getByOepnid("openid");
		//System.out.println(entity.getID());
		//System.out.println(entity.getCreatetime());
		
		BerecommandEntity berecom=new BerecommandEntity();
		//openid, nickname, recommandid
		berecom.setOpenid("11111");
		berecom.setNickname("测试");
		berecom.setRecommandid(13);
		dao.updateScore( berecom);

	}
}
